from typing import List, Optional, Dict, Any, Tuple
from sqlalchemy.ext.asyncio import AsyncSession
from sqlalchemy import select, func
from sqlalchemy.orm import selectinload

from app.models.models import Course, Class
from app.schemas.schemas import CourseCreate


async def get_class_courses(
    db: AsyncSession, 
    class_code: str, 
    school_id: Optional[int] = None,
    week: Optional[int] = None,
    day: Optional[int] = None
) -> Dict[str, Any]:
    """获取班级课程表"""
    # 获取班级信息
    query = select(Class).filter(Class.class_code == class_code)
    if school_id is not None:
        query = query.filter(Class.school_id == school_id)
    
    class_result = await db.execute(query)
    class_obj = class_result.scalars().first()
    
    if not class_obj:
        return None
    
    # 构建课程查询
    query = select(Course).filter(Course.class_code == class_code)
    
    # 添加筛选条件
    if day:
        query = query.filter(Course.day_of_week == day)
    
    # 执行查询
    result = await db.execute(query)
    courses = result.scalars().all()
    
    # 如果指定了周次，过滤课程
    if week:
        filtered_courses = []
        for course in courses:
            weeks_info = course.weeks
            if not (isinstance(weeks_info, dict) and 'start' in weeks_info and 'end' in weeks_info and 'pattern' in weeks_info):
                continue

            start, end, pattern = weeks_info['start'], weeks_info['end'], weeks_info['pattern']
            if not (start <= week <= end):
                continue

            if pattern == 'every' or (pattern == 'odd' and week % 2 != 0) or (pattern == 'even' and week % 2 == 0):
                filtered_courses.append(course)
        courses = filtered_courses
    
    # 构建响应数据
    class_info = {
        "class_code": class_obj.class_code,
        "class_name": class_obj.class_name,
        "start_date": class_obj.start_date,
        "college_name": class_obj.college_name
    }
    
    course_list = []
    for course in courses:
        course_data = {
            "course_name": course.course_name,
            "teacher": course.teacher,
            "day_of_week": course.day_of_week,
            "location": course.location,
            "section": course.section,
            "weeks": course.weeks,
            "courseType": course.courseType
        }
        course_list.append(course_data)
    
    return {
        "class_info": class_info,
        "courses": course_list
    }


async def upsert_course(db: AsyncSession, course: CourseCreate) -> (Course, str):
    """创建或更新课程"""
    # 检查课程是否已存在
    result = await db.execute(
        select(Course).filter_by(class_id=course.class_id, course_name=course.course_name)
    )
    db_course = result.scalars().first()

    if db_course:
        # 更新现有课程
        update_data = course.model_dump(exclude_unset=True)
        for key, value in update_data.items():
            setattr(db_course, key, value)
        db.add(db_course)
        await db.commit()
        await db.refresh(db_course)
        return db_course, "updated"
    else:
        # 创建新课程
        db_course = Course(**course.model_dump())
        db.add(db_course)
        await db.commit()
        await db.refresh(db_course)
        return db_course, "created"


async def bulk_upsert_courses(db: AsyncSession, courses: List[CourseCreate]) -> List[Tuple[Course, str]]:
    """
    批量创建或更新课程（单次事务提交，提升吞吐量）。
    以 (class_id, course_name) 作为幂等键判断更新/插入。
    """
    if not courses:
        return []

    # 收集将处理的键，减少往返
    keys = {(c.class_id, c.course_name) for c in courses}
    class_ids = list({cid for cid, _ in keys})
    course_names = list({name for _, name in keys})

    existing_map: Dict[Tuple[int, str], Course] = {}
    # 分批按 class_id 查询，避免产生 (class_id x course_name) 的全量笛卡尔查询
    for cid in class_ids:
        result = await db.execute(
            select(Course).filter(
                Course.class_id == cid,
                Course.course_name.in_(course_names)
            )
        )
        for row in result.scalars().all():
            existing_map[(row.class_id, row.course_name)] = row

    results: List[Tuple[Course, str]] = []

    # 批量更新/插入（只在末尾统一提交）
    for c in courses:
        key = (c.class_id, c.course_name)
        exist = existing_map.get(key)
        if exist:
            data = c.model_dump(exclude_unset=True)
            for k, v in data.items():
                setattr(exist, k, v)
            db.add(exist)
            results.append((exist, "updated"))
        else:
            new_obj = Course(**c.model_dump())
            db.add(new_obj)
            results.append((new_obj, "created"))

    # 先 flush 获取自增主键等数据库生成字段，再一次性提交
    await db.flush()
    await db.commit()

    return results
async def get_all_courses(
    db: AsyncSession,
    class_id: Optional[int] = None,
    class_code: Optional[str] = None,
) -> List[Course]:
    """获取全部课程（可选按 class_id 或 class_code 过滤，不分页）"""
    query = select(Course)
    if class_id is not None:
        query = query.filter(Course.class_id == class_id)
    if class_code is not None:
        query = query.filter(Course.class_code == class_code)

    result = await db.execute(query.order_by(Course.id))
    courses = result.scalars().all()
    return courses
